/*
 * database.java
 *
 * Created on 18 novembre 2006, 17:28
 *
 * Copyright (C) 2006-2007 Alexis ROBERT
 * Under the terms of the GPL2 license.
 */

package blbarc;

import java.io.File;
import java.util.Iterator;

import java.sql.*;

/**
 * Database abstraction class (HSQL implementation)
 * @author Alexis ROBERT
 */
public class Database {
    Connection conn;
    
    /** Creates a new instance of database */
    public Database() {
        boolean toCreate = false;
        if (!new File("data.properties").exists())
            toCreate = true;
        
        try {
            Class.forName("org.hsqldb.jdbcDriver");
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection("jdbc:hsqldb:file:data", "sa", "");
            conn.setAutoCommit(true);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        if (toCreate == true)
            createTables();
    }

    /** Shutdown the database : sync it, delete locks and close objects */
    public void shutdown() {
        try {
            Statement st = null;
            st = conn.createStatement();
            st.execute("SHUTDOWN");
            st.close();
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }    
    
    /** Sync the database */
    private void checkpoint() {
        try {
            System.out.println(i18n.getInstance().getString("syncingdb"));
            Statement st = null;
            st = conn.createStatement();
            st.execute("CHECKPOINT");
            st.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    /** Create the database */
    public void createTables() {
        Statement st = null;
        System.out.println(i18n.getInstance().getString("creatingdb")); 
        try {
            st = conn.createStatement();
            st.executeUpdate("CREATE TABLE barcodes (id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT BY 1) PRIMARY KEY, barcode VARCHAR, title VARCHAR, UNIQUE (barcode));");
            st.executeUpdate("CREATE TABLE fields (id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1, INCREMENT BY 1) PRIMARY KEY, barcodeid INTEGER, key VARCHAR, value VARCHAR);");
            st.close();
            checkpoint();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    /**
     * Is a barcode exist in the database ?
     * @param barcode Barcode string
     * #return true if the barcode exists and false if it doesn't exist
     */
    public boolean isBarcode(String barcode){
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean found = false;
        try {
            pstmt = conn.prepareStatement("SELECT id FROM barcodes WHERE barcode = ?", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pstmt.setString(1, barcode);
            rs = pstmt.executeQuery();
            rs.last();
            if (rs.getRow() != 0) {
                found = true;
            }
            pstmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return found;
    }
    
    // TODO : FIND A BETTER WAY TO DO SO !
    public Barcode[] getBarcode(String barcode) {
        return getBarcode(barcode, false);
    }
    public Barcode[] getBarcode() {
        return getBarcode("",true);
    }
    public Barcode[] getBarcode(String barcode, Boolean everything) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Barcode[] result = null;
        
        try {
            if (everything == false) {
                pstmt = conn.prepareStatement("SELECT * FROM barcodes WHERE barcode = ?",
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                pstmt.setString(1, barcode);
            } else {
                pstmt = conn.prepareStatement("SELECT * FROM barcodes",
                        ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            }
            
            rs = pstmt.executeQuery();
            rs.last();
            result = new Barcode[rs.getRow()];
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Barcode barcodeobj = new Barcode();
                barcodeobj.id = rs.getInt("id");
                barcodeobj.barcode = rs.getString("barcode");
                barcodeobj.title = rs.getString("title");
                
                PreparedStatement pstmt2 = null;
                ResultSet rs2 = null;
                pstmt2 = conn.prepareStatement("SELECT * FROM fields WHERE barcodeid = ?");
                pstmt2.setInt(1, barcodeobj.id);
                rs2 = pstmt2.executeQuery();
            
                while (rs2.next()) {
                    barcodeobj.fields.put(rs2.getString("key"),rs2.getString("value"));
                }
                
                result[rs.getRow()-1] = barcodeobj;
            }
            rs.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        return result;
    }
    
    /**
     * Save this barcode in the database
     * @param barcode Barcode object to save
     */
    public void saveBarcode(blbarc.Barcode barcode) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("UPDATE barcodes SET title=? WHERE barcode = ?");
            pstmt.setString(1, barcode.title);
            pstmt.setString(2, barcode.barcode);
            pstmt.executeUpdate();
            pstmt.close();
            
            pstmt = conn.prepareStatement("DELETE FROM fields WHERE barcodeid=?");
            pstmt.setInt(1, barcode.id);
            pstmt.executeUpdate();
            pstmt.close();
            
            for (Iterator it = barcode.fields.keySet().iterator(); it.hasNext();) {
                String key = (String)it.next();
                String value = barcode.fields.get(key);
                
                pstmt = conn.prepareStatement("INSERT INTO fields (barcodeid, key, value) VALUES (?,?,?)");
                pstmt.setInt(1, barcode.id);
                pstmt.setString(2, key);
                pstmt.setString(3, value);
                pstmt.executeUpdate();
                pstmt.close();
            }
            checkpoint();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    /**
     * Create an empty barcode
     * @param barcodeTxt Barcode string
     */
    public void createEmptyBarcode(String barcodeTxt) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("INSERT INTO barcodes (barcode, title) VALUES (?, '')");
            pstmt.setString(1, barcodeTxt);
            pstmt.executeUpdate();
            pstmt.close();
            checkpoint();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    /**
     * Delete the specified barcode.
     * @param id Barcode id
     */
    public void deleteBarcode(int id) {
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement("DELETE FROM barcodes WHERE id = ?");
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = conn.prepareStatement("DELETE FROM fields WHERE barcodeid = ?");
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            pstmt.close();
            checkpoint();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}
